# -*- coding: utf-8 -*-
# This code written by ChanGOGOGO
"""
每日场景通话时长
"""
import pymysql
from util import (
    query_es,
    logging,
    write_excel,
    date_slice,
    time,
    ms2min,
    db_ip,
    db_port,
    db_username,
    db_password,
    excel_save_dir,
)


class ExportDailySceneBills:
    def __init__(self, ui_data):
        self.data_date = ui_data["data_date"]
        self.business = ui_data.get("business")
        self.data_type = ui_data.get("data_type")
        self.call_type = ui_data.get("call_type")
        self.start_time = ui_data.get("start_time")
        self.end_time = ui_data.get("end_time")
        self.scene_names = ui_data.get("target_datas")
        company_id = ui_data.get("company_id")
        self.db_name = f"yicall_{company_id}"
        self.table_index = ui_data.get("business_id")
        if self.call_type == "呼出":
            self.es_index = f"{self.db_name}_{self.table_index}_callout_session_ach_{self.data_date}"
        else:
            self.es_index = f"{self.db_name}_{self.table_index}_callout_session_in_ach_{self.data_date}"
        self.conn = pymysql.connect(
            host=db_ip,
            port=db_port,
            user=db_username,
            passwd=db_password,
            charset="utf8",
        )
        self.cursor = self.conn.cursor()

    def query_es_data(self, task_ids, day_start_time, day_end_time):
        """"""
        if len(task_ids) < 1:
            body = {
                "query": {
                    "bool": {
                        "must": [
                            {"term": {"calloutConnectStatus": 100}},
                            {
                                "range": {
                                    "calloutFinishTime": {
                                        "gte": day_start_time,
                                        "lte": day_end_time,
                                    }
                                }
                            },
                        ]
                    }
                }
            }
        else:
            body = {
                "query": {
                    "bool": {
                        "must": [
                            {"term": {"calloutConnectStatus": 100}},
                            {
                                "range": {
                                    "calloutFinishTime": {
                                        "gte": day_start_time,
                                        "lte": day_end_time,
                                    }
                                }
                            },
                            {"terms": {"taskId": task_ids}},
                        ]
                    }
                }
            }
        fields = ["calloutDialTime", "taskId", "dialogDuration"]
        day_es_data = query_es(self.es_index, body, fields)
        return day_es_data

    def export(self):
        # 从数据库中查询出各场景下有哪些任务id
        scene_task_info = dict()  # 场景任务信息，一个场景下有多少个任务
        task_scene_info = dict()  # 任务场景信息，一个任务对应一个场景
        if self.call_type == "呼出":
            sql = f"""select a.task_id,b.scene_name from {self.db_name}.b_{self.table_index}_callout_task a 
            INNER JOIN {self.db_name}.b_{self.table_index}_call_scene b on a.scene_id=b.scene_id;"""
        else:
            sql = f"""select a.task_id,b.scene_name from {self.db_name}.b_{self.table_index}_callout_task_in a 
            INNER JOIN {self.db_name}.b_{self.table_index}_call_scene b on a.scene_id=b.scene_id;"""
        self.cursor.execute(sql)
        query_db_result = self.cursor.fetchall()
        for x in query_db_result:
            task_id, scene_name = x
            # 维护场景任务信息
            find_scene_name = scene_task_info.get(scene_name)
            if find_scene_name is None:
                scene_task_info[scene_name] = [task_id]
            else:
                scene_task_info[scene_name].append(task_id)
            # 维护任务场景信息
            task_scene_info[task_id] = scene_name
        self.cursor.close()
        self.conn.close()
        # 根据指定的场景，判断场景下哪些任务需要查询
        target_scene_num = len(self.scene_names)
        task_ids = list()
        if target_scene_num > 0:
            for scene_name in self.scene_names:
                single_scene_task_ids = scene_task_info.get(scene_name)
                task_ids += single_scene_task_ids
        bills = dict()
        # 以“天”为单位查询es数据，避免scroll超时
        date_slice_result = date_slice(self.start_time, self.end_time)
        handle_data_start_time = time.time()
        for everyday in date_slice_result:
            day_start_time = everyday[0]
            day_end_time = everyday[1]
            day_es_data = self.query_es_data(task_ids, day_start_time, day_end_time)
            for data in day_es_data:
                dial_date = data.get("calloutDialTime")[:10]
                dialog_duration = data.get("dialogDuration")
                dialog_duration_min = ms2min(int(dialog_duration))
                es_task_id = data.get("taskId")
                db_scene_name = task_scene_info.get(int(es_task_id))
                find_dial_data = bills.get(dial_date, -1)
                if find_dial_data != -1:
                    find_scene_name = bills.get(dial_date).get(db_scene_name, -1)
                    if find_scene_name != -1:
                        bills[dial_date][db_scene_name][
                            "total_dialog_duration"
                        ] += dialog_duration_min
                        bills[dial_date][db_scene_name]["total_connected"] += 1
                    else:
                        bills[dial_date][db_scene_name] = {
                            "total_connected": 1,
                            "total_dialog_duration": dialog_duration_min,
                        }
                else:
                    bills[dial_date] = {
                        db_scene_name: {
                            "total_connected": 1,
                            "total_dialog_duration": dialog_duration_min,
                        }
                    }
        handle_data_end_time = time.time()
        handle_data_take_time = handle_data_end_time - handle_data_start_time
        logging.info(
            f"处理从ElasticSearch中查询出来的数据共计耗时{handle_data_take_time:.2f}秒。"
        )
        if bills:
            # 分批次查询和写入数据
            excel_names = list()
            excel_data = list()
            sorted_data = sorted(
                bills
            )  # 按照bills的key（dial_date通话日期）进行排序，写入excel的数据就是正序
            for date in sorted_data:
                scene_call_info = bills.get(date)
                for scene_name, detail_info in scene_call_info.items():
                    total_connected = detail_info.get("total_connected")
                    total_dialog_duration = detail_info.get("total_dialog_duration")
                    excel_data.append(
                        [date, scene_name, total_connected, total_dialog_duration]
                    )
            excel_data.append(["总计", "", "", f"=SUM(D2:D{len(excel_data) + 1})"])
            excel_title = ["日期", "场景", "接通数", "通话时长(分钟)"]
            file_name = f"{self.business}_({self.call_type}){self.data_type}"
            write_excel_start_time = time.time()
            excel_name = write_excel(
                [[self.data_type, excel_title, excel_data]], excel_save_dir, file_name
            )
            write_excel_end_time = time.time()
            write_excel_take_time = write_excel_end_time - write_excel_start_time
            logging.info(
                f"将{self.business}的{self.data_type}写入excel共计耗时{write_excel_take_time:.2f}秒。"
            )
            excel_names.append(excel_name)
            return {"code": 0, "excel_names": excel_names, "msg": None}
        else:
            msg = (
                f"<font color=green>无法导出“{self.business}”的【{self.data_type}】，"
                f"原因：\n在{self.start_time}至{self.end_time}没有{self.call_type}数据！</font>"
            )
            logging.info(msg)
            return {"code": 1, "excel_names": [], "msg": msg}


if __name__ == "__main__":
    ...
